﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LF3.Moduls;
using CP.DBService;
using Oracle.ManagedDataAccess.Client;
using System.Data;

namespace LF3.Repositories
{
    public class LF_CurrentHeatRepository
    {
        DBHelper db = DBHelper.CreateInstance();
        public V_Schedule_Tapping GetCurrentHeatInfo()
        {
            return db.ExecuteSingleObject<V_Schedule_Tapping>(@"select * from V_Schedule_Tapping");
        }

        public string GetStnoByHeatID(string heatid)
        {
            return db.ExecuteScalar<string>(string.Format(@"select STEELGRADE from LF_SCHEDULE where heatid = '{0}' and rownum =1",heatid));
        }

        public Lf_Cc_Status_Info GetCCInfo(int ccid)
        {
            return db.ExecuteSingleObject<Lf_Cc_Status_Info>(string.Format(@"select * from Lf_Cc_Status_Info
                                                    where ccid= {0}", ccid));
        }

        public DataTable GetHeatEvent(string heatid)
        {
            return db.ExecuteDataTable(string.Format(@"select t1.EVENTCODE,t2.EVENTNAME,t1.INSERT_TIME 
                                        from LF_HEAT_EVENT t1,LF_EVENT_CODE t2 
                                        where t1.eventcode = t2.eventcode and t1.heatid = '{0}'
                                         order by insert_time desc ", heatid));
        }

        public int GetHeatSampleCount(string heatid)
        {
            return db.ExecuteScalar<int>(string.Format(@"select max(sample_count) from 
                                lf_sample_count where heatid = '{0}'", heatid));
        }

        public DataTable GetHeatStandard(string stno)
        {
            return db.ExecuteDataTable(string.Format(@"select * from LF_HEAT_STANDARD where ST_NO = '{0}' and (PROC_ID = 'L' or PROC_ID = 'C')", stno));
        }

        public void AddSampleEvent(string heatid, int samplecount)
        {
            try
            {
                db.BeginTransaction();
                db.ExecuteNonQuery(string.Format(@"insert into lf_sample_count(heatid,sample_count)     
                                            values('{0}',{1})", heatid, samplecount));
            
                db.ExecuteNonQuery(string.Format(@"INSERT INTO LF_HEAT_EVENT (EVENTCODE,
                           BOFSCHEDLEID,
                           HEATID,
                           PONO,
                           STEELGRADENO,
                           LADLEID,
                           AGGREGATETYPE,
                           AGGREGATENO,
                           EVENTTIME,QYCOUNT)
                           SELECT 4506 EVENTCODE,
                                  BOFSCHEDLEID,
                                  HEATID,
                                  PONO,
                                  STEELGRADENO,
                                  LADLEID,
                                  4 AGGREGATETYPE,
                                  5 AGGREGATENO,
                                  TO_CHAR (SYSDATE, 'yyyymmddhh24miss') EVENTTIME,
                                  {1} QYCOUNT
                             FROM LF_CURHEATS
                            WHERE heatid = '{0}'", heatid, samplecount));
                db.Commit();
            }
            catch (Exception ex)
            {
                db.Rollback();
                throw ex;
            }
        }

        public void AddHeatMatConsume(Lf_Material_Consume mat)
        {
            int qty = db.ExecuteScalar<int>(string.Format(@"select MATERIALQUANTITY from LF_MATERIAL_CONSUME 
                                        where BOFSCHEDLEID = {0} and AGGREGATETYPE = '{1}' and AGGREGATENO = {2}
                                                and MATERIALCODE = '{3}'", mat.bofschedleid, mat.aggregatetype,
                                                                         mat.aggregateno, mat.materialcode));
            if (qty == 0)
                db.ExecuteNonQuery(string.Format(@"insert into LF_MATERIAL_CONSUME(BOFSCHEDLEID,HEATID,
                                        AGGREGATETYPE,AGGREGATENO,MATERIALCODE,MATERIALDESC,MATERIALQUANTITY,
                                        BUNKERNO) values({0},'{1}','{2}',{3},'{4}','{5}',{6},'{7}')", mat.bofschedleid,
                                        mat.heatid, mat.aggregatetype, mat.aggregateno, mat.materialcode, mat.materialdesc,
                                        mat.materialquantity, mat.bunkerno));
            else
                db.ExecuteNonQuery(string.Format(@"update LF_MATERIAL_CONSUME set MATERIALQUANTITY = MATERIALQUANTITY +
                                    {0} where BOFSCHEDLEID = {1} and AGGREGATETYPE = '{2}' and AGGREGATENO = {3}
                                        and MATERIALCODE = '{4}'", mat.materialquantity, mat.bofschedleid, mat.aggregatetype,
                                                                 mat.aggregateno, mat.materialcode));
        }

        public void UpdateHeatMatConsume(Lf_Material_Consume mat)
        {
            db.ExecuteNonQuery(string.Format(@"update LF_MATERIAL_CONSUME set MATERIALQUANTITY = 
                                    {0},BUNKERNO='99' where BOFSCHEDLEID = {1} and AGGREGATETYPE = '{2}' and AGGREGATENO = {3}
                                        and MATERIALCODE = '{4}'", mat.materialquantity, mat.bofschedleid, mat.aggregatetype,
                                                             mat.aggregateno, mat.materialcode));
        }

        public void DeleteHeatMatConsume(Lf_Material_Consume mat)
        {

            db.ExecuteNonQuery(string.Format(@"delete LF_MATERIAL_CONSUME where BOFSCHEDLEID = {0} and 
                                                AGGREGATETYPE = '{1}' and AGGREGATENO = {2}
                                        and MATERIALCODE = '{3}'", mat.bofschedleid, mat.aggregatetype,
                                                                 mat.aggregateno, mat.materialcode));
        }
    }
}
